CS157a Fall 2018Practice Midterm 2

Studying for one of my tests does involve some memorization. I believe this is an important skill. Often people waste a lot of time and fail to remember the things they are trying to memorize. Please use a technique that has been shown to work such as the method of loci. Other memorization techniques can be found off the Wiki Page for Moonwalking with Einstein. Given this, to study for the midterm I would suggest you:

• Know how to do (by heart) all the practice problems.
• Go over your notes at least three times. Second and third time try to see how much you can remember from the first time.
• Go over the homework problems.
• Try to create your own problems similar to the ones I have given and solve them.
• Skim the relevant sections from the book.
• If you want to study in groups, at this point you are ready to quiz each other.

The practice midterm is below. Here are some facts about the actual midterm: (a) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (b) You should bring photo ID. (c) There will be more than one version of the test. Each version will be of comparable difficulty. (d) One problem (less typos) on the actual test will be from the practice test.

1. Given the relation `R(A, B, C, D, E)` and FDs `AB->C`, `AC->B`, and `AE->D` show how the 3NF Synthesis algorithm from class would output a 3NF decomposition.
2. Give an example of a set of relations and dependencies which is in BCNF but not 4NF.
3. Draw an example of the following as both an E/R and UML diagram: two related/associated entities/classes in a many one relationship where the relationship has two attributes.
4. Define the following kinds of constraints and show with a diagram how to represent it in the E/R model: (a) key, (b) referential integrity constraint, (c) degree constraint.
5. Explain how or E/R to relational model mapping would map a Weak Entity set and its supporting entity to relations.
6. Explain how a many-one relationship could be represented in ODL.
7. Express the following queries in relational algebra (assuming the movie database relations from the book): (a) for each year, total number of movies made in that year, sorted by increasing year, (b) Stars and the average year of the movies they appear in.
8. Explain the following Datalog concepts: (a) intentional database, (b) extensional database, (c) a variable appearing safely in a rule.
9. Express the following queries in SQL: (a) name and square of salary as sal2 of all movie executives making less than $1million, (b) All movies produced by MGM in increasing order of length.
10. Give an example of a correlated and an example of an uncorrelated query, explain why your examples work.